using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using FileInfo = System.IO.FileInfo;
using File = System.IO.File;
using StreamWriter = System.IO.StreamWriter;

using OdbcConnection = System.Data.Odbc.OdbcConnection;
using OdbcCommand = System.Data.Odbc.OdbcCommand;
using OdbcDataReader = System.Data.Odbc.OdbcDataReader;
using OdbcException = System.Data.Odbc.OdbcException;

using OleDbConnection = System.Data.OleDb.OleDbConnection;
using OleDbCommand = System.Data.OleDb.OleDbCommand;
using OleDbDataReader = System.Data.OleDb.OleDbDataReader;
using OleDbException = System.Data.OleDb.OleDbException;

using StringBuilder = System.Text.StringBuilder;

using BackgroundWorker = System.ComponentModel.BackgroundWorker;

namespace SqlBuddy
{
    public partial class Main : Form
    {
        private BindingList<BuddyConnection> dbConnList;
        private ConnectionStringManager dbConns;
        private ConnectionEditor connectionEditor;
        private AboutSqlBuddyBox about;
        private RegistrySettings registrySettings;
        private string currentConnectionString = null;
        public Main()
        {
            InitializeComponent();
            dbConnList = new BindingList<BuddyConnection>();
            dbConns = new ConnectionStringManager();
            AvailableConnections.DataSource = dbConnList;
            AvailableConnections.DisplayMember = "Name";
            connectionEditor = new ConnectionEditor();
            about = new AboutSqlBuddyBox();

            registrySettings = new RegistrySettings();
            registrySettings.Load();

            foreach (string s in Enum.GetNames(Type.GetType("SqlBuddy.InputSource")))
                SelectedInputSource.Items.Add(s);
            SelectedInputSource.SelectedItem = registrySettings.Get("InputSource");

            foreach (string s in Enum.GetNames(Type.GetType("SqlBuddy.OutputDestination")))
                SelectedOutputDestination.Items.Add(s);
            SelectedOutputDestination.SelectedItem = registrySettings.Get("OutputTarget");

            inputFilename.Text = registrySettings.Get("InputFilePath");
            outputFilename.Text = registrySettings.Get("OutputFilePath");

            if (registrySettings.Get("ConnectionPath") != null)
                LoadConnections(registrySettings.Get("ConnectionPath"));

            if (registrySettings.Get("ConnectionFile") != null)
                dbConns.Load(registrySettings.Get("ConnectionFile"));

            if (registrySettings.Get("OutputWindowWordWrap") != null)
                queryInputTextBox.WordWrap = bool.Parse(registrySettings.Get("OutputWindowWordWrap"));

            if (registrySettings.Get("InputWindowWordWrap") != null)
                queryResultTextBox.WordWrap = bool.Parse(registrySettings.Get("InputWindowWordWrap"));

            if (registrySettings.Get("ErrorMessagesWordWrap") != null)
                errorMessagesTextBox.WordWrap = bool.Parse(registrySettings.Get("ErrorMessagesWordWrap"));

            statusBarMessage.Text = "Ready";
            notifyIconMenu.Enabled = true;
        }
        private void AddMessage(Exception ex)
        {
            errorMessagesTextBox.AppendText(
                DateTime.Now.ToShortDateString() + " " +
                DateTime.Now.ToShortTimeString() + "\n");
                
            errorMessagesTextBox.SelectionIndent = 20;
            errorMessagesTextBox.AppendText(
                ex.Source + "\n\t" +
                ex.Message + "\n");
            errorMessagesTextBox.SelectionIndent = 0;
        }
        private void AddMessage(string p)
        {
            errorMessagesTextBox.AppendText(
                DateTime.Now.ToShortDateString() + " " +
                DateTime.Now.ToShortTimeString() + "\t" +
                p + "\n");
        }
        /// <summary>
        /// Quit
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void quitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        /// <summary>
        /// Help the user select an input file for the source
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void InputFileSelector_Click(object sender, EventArgs e)
        {
            DialogResult d = inputFileSelectorDialog.ShowDialog();
            if (d == DialogResult.OK)
                inputFilename.Text = inputFileSelectorDialog.FileName;
        }
        /// <summary>
        /// Run the query from the specified source, and place the result in the
        /// specified destination.  Also validates connection, input and output
        /// before running.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Execute_Click(object sender, EventArgs e)
        {
            try
            {
                Execute.Enabled = false;

                //Validate connections
                if (AvailableConnections.SelectedItem == null)
                    throw (new ApplicationException("Please select a connection"));

                currentConnectionString = ((BuddyConnection)AvailableConnections.SelectedItem).ConnectionString;

                //get output destination
                string destination = null;
                if (SelectedOutputDestination.SelectedItem != null)
                    destination = SelectedOutputDestination.SelectedItem.ToString();
                OutputDestination o =
                    (OutputDestination)Enum.Parse(System.Type.GetType("SqlBuddy.OutputDestination", true),
                    destination);

                //Validate output destination
                if
                    (!(
                    o == OutputDestination.Clipboard ||
                    o == OutputDestination.File ||
                    o == OutputDestination.OutputWindow
                    ))
                    throw (new ApplicationException("Please select an output source"));
                RunQueryArgs r = new RunQueryArgs();
                r.OutputDest = o;

                //Validate input source
                string source = null;
                if (SelectedInputSource.SelectedItem != null)
                    source = SelectedInputSource.SelectedItem.ToString();
                InputSource i =
                    (InputSource)Enum.Parse(System.Type.GetType("SqlBuddy.InputSource", true),
                    source);

                if (i == InputSource.File)
                    r.Query = File.ReadAllText(inputFilename.Text);
                else if (i == InputSource.Clipboard)
                    r.Query = Clipboard.GetText();
                else if (i == InputSource.InputWindow)
                    r.Query = queryInputTextBox.Text;
                else
                    throw(new ApplicationException("Please select an input source"));

                queryExecutionThread.RunWorkerAsync(r);
            }
            catch (ApplicationException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        /// <summary>
        /// Toggle word-wrap on the input or output windows, depending on what's visible(doesn't work)
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void wordWrapToolStripMenuItem_Click(object sender, EventArgs e)
        {
            RichTextBox textBoxToToggle = null;
            if (mainTabControl.SelectedTab == query)
                textBoxToToggle = queryInputTextBox;
            else if (mainTabControl.SelectedTab == result)
                textBoxToToggle = queryResultTextBox;
            else if (mainTabControl.SelectedTab == messages)
                textBoxToToggle = errorMessagesTextBox;

            if (textBoxToToggle == null)
                statusBarMessage.Text = "Nothing selected for word wrap toggle";
            else
            {
                if (textBoxToToggle.WordWrap == true)
                {
                    textBoxToToggle.WordWrap = false;
                    wordWrapToolStripMenuItem.Checked = false;
                }
                else
                {
                    textBoxToToggle.WordWrap = true;
                    wordWrapToolStripMenuItem.Checked = true;
                }

                if (textBoxToToggle == queryInputTextBox)
                    registrySettings.Set("InputWindowWordWrap", textBoxToToggle.WordWrap.ToString());
                else if (textBoxToToggle == queryResultTextBox)
                    registrySettings.Set("OutputWindowWordWrap", textBoxToToggle.WordWrap.ToString());
                else if (textBoxToToggle == errorMessagesTextBox)
                    registrySettings.Set("ErrorMessagesWordWrap", errorMessagesTextBox.WordWrap.ToString());
            }
        }
        /// <summary>
        /// update the type of source to use for input
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void selectedInputSource_SelectedIndexChanged(object sender, EventArgs e)
        {
            registrySettings.Set("InputSource", ((ComboBox)sender).SelectedItem.ToString());
        }
        /// <summary>
        /// Update the type of destination to use for output
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void selectedOutputDestination_SelectedIndexChanged(object sender, EventArgs e)
        {
            registrySettings.Set("OutputTarget", ((ComboBox)sender).SelectedItem.ToString());
        }
        /// <summary>
        /// Update the filename to use for input
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void inputFilename_TextChanged(object sender, EventArgs e)
        {
            registrySettings.Set("InputFilePath", inputFilename.Text);
        }
        /// <summary>
        /// Update the filename to use for output
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void outputFilename_TextChanged(object sender, EventArgs e)
        {
            registrySettings.Set("OutputFilePath", outputFilename.Text);
        }
        /// <summary>
        /// Help the user select an output file destination
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void outputFileSelector_Click(object sender, EventArgs e)
        {
            DialogResult d = outputFileSelectorDialog.ShowDialog();
            if (d == DialogResult.OK)
                outputFilename.Text = outputFileSelectorDialog.FileName;
        }

        private void mainTabControl_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (mainTabControl.SelectedTab == query)
            {
                wordWrapToolStripMenuItem.Enabled = true;
                wordWrapToolStripMenuItem.Checked = queryResultTextBox.WordWrap;
            }
            else if (mainTabControl.SelectedTab == result)
            {
                wordWrapToolStripMenuItem.Enabled = true;
                wordWrapToolStripMenuItem.Checked = queryInputTextBox.WordWrap;
            }
            else if (mainTabControl.SelectedTab == messages)
            {
                wordWrapToolStripMenuItem.Enabled = true;
                wordWrapToolStripMenuItem.Checked = errorMessagesTextBox.WordWrap;
            }
            else if (mainTabControl.SelectedTab == connection)
            {
                wordWrapToolStripMenuItem.Enabled = false;
                wordWrapToolStripMenuItem.Checked = false;
            }
            else
            {
                throw (new ApplicationException("Unrecognized tab selected"));
            }
        }

        private void aboutToolStripMenuItem_Click(object sender, EventArgs e)
        {
            about.ShowDialog();
        }

        private void Main_FormClosing(object sender, FormClosingEventArgs e)
        {
            notifyIconMenu.Enabled = false;

            registrySettings.Save();
            if (registrySettings.Get("ConnectionPath") != null)
                SaveConnections(registrySettings.Get("ConnectionPath"));
            if (registrySettings.Get("ConnectionFile") != null)
                dbConns.Save();
        }

        private void clearResultsButton_Click(object sender, EventArgs e)
        {
            queryResultTextBox.Clear();
        }

        private void clearErrorMessagesButton_Click(object sender, EventArgs e)
        {
            errorMessagesTextBox.Clear();
        }
#region "Connection Management"
        /// <summary>
        /// Create a new connection
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void newConnection_Click(object sender, EventArgs e)
        {
            AvailableConnections.BeginUpdate();
            dbConnList.Add(new BuddyConnection());
            AvailableConnections.EndUpdate();
        }
        #region "move to ConnectionStringManager"
        /// <summary>
        /// Initialize a folder with connections files
        /// </summary>
        /// <param name="folder"></param>
        private void InitializeConnectionsFiles(string folder)
        {
            using (StreamWriter iniFile = new StreamWriter(File.OpenWrite(folder + "\\schema.ini")))
            {
                iniFile.Write(@"[connections.txt]
Format=TabDelimited
ColNameHeader=True
MaxScanRows=0
CharacterSet=ANSI");
            }
            InitializeConnectionsFile(folder);
        }

        private void InitializeConnectionsFile(string folder)
        {
            using (StreamWriter conns = new StreamWriter(File.OpenWrite(ConnectionsFilename(folder))))
            {
                conns.Write("name	connectionString");
            }
        }
        private string ConnectionsFilename(string folder)
        {
            return (folder + "\\connections.txt");
        }

        /// <summary>
        /// Load the connections stored in the supplied directory
        /// </summary>
        /// <param name="folder"></param>
        private void LoadConnections(string folder)
        {
            using (OleDbConnection c = new OleDbConnection())
            {
                c.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + folder + ";" +
                    "Extended Properties=\"text;HDR=Yes;FMT=Delimited\";";

                c.Open();

                string qry = "select * from connections.txt";
                using (OleDbCommand cmd = new OleDbCommand(qry, c))
                {
                    OleDbDataReader dr = null;
                    try
                    {
                        dr = cmd.ExecuteReader();
                    }
                    catch (OleDbException ex)
                    {
                        if (ex.Message.Contains("The Microsoft Jet database engine could not find the object 'connections.txt'."))
                        {
                            DialogResult d = MessageBox.Show("Folder is not a valid container for connections.  Would you like to create the required files?", "Create connection files", MessageBoxButtons.YesNo);
                            if (d == DialogResult.Yes)
                            {
                                InitializeConnectionsFiles(folder);
                                dr = cmd.ExecuteReader();
                                statusBarMessage.Text = "Connection database files created in " + folder;
                                AddMessage(statusBarMessage.Text);
                                mainTabControl.SelectedTab = messages;
                            }
                            else
                            {
                                statusBarMessage.Text = "Error loading connections";
                                AddMessage(ex);
                                mainTabControl.SelectedTab = messages;
                                return;
                            }
                        }
                        else
                        {
                            statusBarMessage.Text = "Error loading connections";
                            AddMessage(ex);
                            mainTabControl.SelectedTab = messages;
                            return;
                        }
                    }
                    BuddyConnection b;
                    if (dr.HasRows)
                    {
                        AvailableConnections.BeginUpdate();
                        dbConnList.Clear();
                        while (dr.Read() == true)
                        {
                            b = new BuddyConnection();
                            b.Name = dr[0].ToString();
                            b.ConnectionString = dr[1].ToString();
                            dbConnList.Add(b);
                        };
                        AvailableConnections.EndUpdate();
                    }
                    dr.Close();
                    dr.Dispose();
                }
            }
        }
        private void SaveConnections(string folder)
        {
            string tempFilename = ConnectionsFilename(folder) + ".temp";
            try
            {
                if (File.Exists(tempFilename))
                {
                    File.Delete(tempFilename);
                }
                File.Move(ConnectionsFilename(folder), tempFilename);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error saving connections: " + ex.Message);
                return;
            }


            try
            {
                InitializeConnectionsFile(folder);
                StringBuilder newConnectionText = new StringBuilder();
                foreach (BuddyConnection b in dbConnList)
                {
                    newConnectionText.AppendLine(b.Name + "\t" + b.ConnectionString);
                }
                File.AppendAllText(ConnectionsFilename(folder), "\n" + newConnectionText.ToString());
            }
            catch (Exception ex)
            {
                if (File.Exists(ConnectionsFilename(folder)))
                {
                    File.Delete(ConnectionsFilename(folder));
                }
                File.Move(tempFilename, ConnectionsFilename(folder));
            }
        }
        #endregion
        private void AvailableConnections_DoubleClick(object sender, EventArgs e)
        {
            EditConnection();
        }
        /// <summary>
        /// Delete a connection
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void deleteConnectionButton_Click(object sender, EventArgs e)
        {
            DeleteConnection();
        }
        /// <summary>
        /// Delete a connection
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void deleteConnection_Click(object sender, EventArgs e)
        {
            DeleteConnection();
        }
        /// <summary>
        /// Edit an individual connection
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void editConnection_Click(object sender, EventArgs e)
        {
            EditConnection();
        }
        private void EditConnection()
        {
            int selectedConn = AvailableConnections.SelectedIndex;
            if (selectedConn >= 0)
            {
                BuddyConnection connToEdit = dbConnList[selectedConn];
                connectionEditor.ConnToEdit = (BuddyConnection)(connToEdit.Clone());
                DialogResult result = connectionEditor.ShowDialog();
                if (result == DialogResult.OK)
                {
                    AvailableConnections.BeginUpdate();
                    dbConnList[selectedConn] = connectionEditor.ConnToEdit;
                    AvailableConnections.EndUpdate();
                }
            }
            else
            {
                statusBarMessage.Text = "Please select a connection";
            }
        }
        /// <summary>
        /// Delete the currently selected connection
        /// </summary>
        private void DeleteConnection()
        {
            int selectedConn = AvailableConnections.SelectedIndex;
            if (selectedConn >= 0)
            {
                AvailableConnections.BeginUpdate();
                dbConnList.RemoveAt(selectedConn);
                AvailableConnections.EndUpdate();
            }
        }
        /// <summary>
        /// Update the path the application uses to store connections
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void configureToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (folderBrowserDialog2.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    if (registrySettings.Get("ConnectionPath") != null)
                        SaveConnections(registrySettings.Get("ConnectionPath"));

                    LoadConnections(folderBrowserDialog2.SelectedPath);
                    registrySettings.Set("ConnectionPath", folderBrowserDialog2.SelectedPath);
                }
                catch (OleDbException ex)
                {
                    statusBarMessage.Text = "Error opening connections";
                    AddMessage(ex);
                    mainTabControl.SelectedTab = messages;
                }
            }
        }
        #endregion
#region "Notify Icon"
        /// <summary>
        /// Exit the program from the notify icon context menu
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void notifyIconExitProgram_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        /// <summary>
        /// Hide the window and its taskbar entry
        /// </summary>
        private void HideWindow()
        {
            this.Visible = false;
            notifyIconShowHideWindow.Text = "&Show";
        }
        /// <summary>
        /// Make the window visible after having been hidden
        /// </summary>
        private void ShowWindow()
        {
            this.Visible = true;
            notifyIconShowHideWindow.Text = "&Hide";
        }
        /// <summary>
        /// Toggle window visibility from the notify icon context menu
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void notifyIconShowHideWindow_Click(object sender, EventArgs e)
        {
            if (this.Visible == true)
                HideWindow();
            else
                ShowWindow();
        }
        /// <summary>
        /// Toggle window visibility when the notify icon is double-clicked
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void notifyIcon1_DoubleClick(object sender, EventArgs e)
        {
            switch (this.WindowState)
            {
                case FormWindowState.Minimized:
                    this.WindowState = FormWindowState.Normal;
                    ShowWindow();
                    break;
                case FormWindowState.Normal:
                    if (this.Visible == true)
                        HideWindow();
                    else
                        ShowWindow();
                    break;
                case FormWindowState.Maximized:
                    if (this.Visible == true)
                        HideWindow();
                    else
                        ShowWindow();
                    break;
            }
        }
        #endregion
#region "Query Execution Thread"

        /// <summary>
        /// Runs the supplied query
        /// </summary>
        /// <param name="qry"></param>
        /// <returns></returns>
        private StringBuilder OleDbRunQuery(string qry)
        {
            queryExecutionThread.ReportProgress(5, new RunQueryProgress("Connecting..."));
            StringBuilder result = new StringBuilder();
            using (OleDbConnection c = new OleDbConnection())
            {
                c.ConnectionString = currentConnectionString;
                c.Open();

                using (OleDbCommand cmd = new OleDbCommand(qry, c))
                {
                    queryExecutionThread.ReportProgress(20, new RunQueryProgress("Executing..."));
                    try
                    {
                        using (OleDbDataReader dr = cmd.ExecuteReader())
                        {
                            queryExecutionThread.ReportProgress(25, new RunQueryProgress("Retrieving Results..."));
                            do
                            {
                                if (dr.HasRows)
                                {
                                    //column headers
                                    int ct = dr.FieldCount;
                                    for (int i = 0; i < ct - 1; i++)
                                    {
                                        result.Append(dr.GetName(i));
                                        result.Append('\t');
                                    }
                                    result.AppendLine(dr.GetName(ct - 1));

                                    //table contents
                                    while (dr.Read() == true)
                                    {
                                        for (int i = 0; i < ct - 1; i++)
                                        {
                                            result.Append(dr[i].ToString());
                                            result.Append('\t');
                                        }
                                        result.AppendLine(dr[ct - 1].ToString());
                                        System.Threading.Thread.Sleep(0);
                                    };
                                }
                            } while (dr.NextResult());


                            dr.Close();
                            dr.Dispose();
                            cmd.Dispose();
                        }
                        queryExecutionThread.ReportProgress(100, new RunQueryProgress("Query executed successfully " +
                            DateTime.Now.ToShortDateString() + " " +
                            DateTime.Now.ToShortTimeString()));
                    }
                    catch (OleDbException ex)
                    {
                        queryExecutionThread.ReportProgress(100, new RunQueryProgress("Error running query", ex));
                    }
                }
            }
            return (result);
        }
        private void queryExecutionThread_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            RunQueryProgress p = (RunQueryProgress)(e.UserState);
            progressBar.Value = e.ProgressPercentage;
            statusBarMessage.Text = p.Message;
            if (p.Error != null)
                AddMessage(p.Error);
        }

        private void queryExecutionThread_DoWork(object sender, DoWorkEventArgs e)
        {
            RunQueryArgs r = (RunQueryArgs)(e.Argument);
            RunQueryResult result = new RunQueryResult();
            result.OutputDest = r.OutputDest;

            result.Result = OleDbRunQuery(r.Query);
            e.Result = result;
        }

        private void queryExecutionThread_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            RunQueryResult execResult = (RunQueryResult)(e.Result);
            mainTabControl.SelectedTab = result;

            //return the output
            if (execResult.OutputDest == OutputDestination.OutputWindow)
            {
                queryResultTextBox.AppendText(execResult.Result.ToString());
            }
            else if (execResult.OutputDest == OutputDestination.Clipboard)
            {
                Clipboard.SetText(execResult.Result.ToString());
            }
            else
            {
                File.WriteAllText(outputFilename.Text, execResult.Result.ToString());
            }
            Execute.Enabled = true;
        }
#endregion
    }

}
